package org.anderground.app.storage.db;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;

import org.anderground.app.storage.ConfigData;
import org.anderground.app.storage.ConfigReader;
import org.anderground.app.storage.OnProgressCallback;
import org.anderground.app.storage.db.DbStorage.CitiesTable;
import org.anderground.app.storage.db.DbStorage.CityAliasesTable;
import org.anderground.app.storage.db.DbStorage.CountriesTable;
import org.anderground.core.Logger;
import org.anderground.core.model.IdBindingModelException;
import org.anderground.core.storage.StorageException;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author Eldar Abusalimov
 */
public class DbConfigReader extends AbstractDbReader<ConfigData> implements
		ConfigReader {

	private OnProgressCallback callback;

	/**
	 * 
	 * @param db
	 * @param onProgressCallback
	 */
	public DbConfigReader(SQLiteDatabase db,
			OnProgressCallback onProgressCallback) {
		super(db);
		callback = onProgressCallback;
	}

	/**
	 * 
	 * @param openHelper
	 * @param onProgressCallback
	 */
	public DbConfigReader(SQLiteOpenHelper openHelper,
			OnProgressCallback onProgressCallback) {
		super(openHelper);
		callback = onProgressCallback;
	}

	@Override
	protected ConfigData readFromInput(SQLiteDatabase db) throws IOException {
		Cursor cursor = db.rawQuery("SELECT * FROM "
				+ DbStorage.TABLE_SETUP_INFO, null);
		boolean setupOk = cursor.getCount() > 0;
		cursor.close();
		if (!setupOk) {
			throw new StorageException("config table is empty");
		}

		ConfigData.Builder builder = new ConfigData.Builder();
		selectCountries(db, builder, getOnProgressCallback());
		selectCities(db, builder, getOnProgressCallback());
		try {
			return builder.build();
		} catch (IdBindingModelException e) {
			throw new StorageException("config building failed", e);
		}
	}

	/**
	 * @param db
	 * @param builder
	 * @param c
	 */
	private static void selectCountries(SQLiteDatabase db,
			ConfigData.Builder builder, OnProgressCallback c) {
		final String tCountries = CountriesTable.NAME;

		final String tCountries_Code = CountriesTable.Columns.CODE;
		final String tCountries_Name = CountriesTable.Columns.NAME;

		String[] columns = new String[] { tCountries_Code, tCountries_Name };
		Cursor cursor = db.query(tCountries, columns, null, null, null, null,
				null);
		if (c != null) {
			c.onProgress("(countries)");
		}
		try {
			final int tCountries_Code_Index = cursor
					.getColumnIndexOrThrow(tCountries_Code);
			final int tCountries_Name_Index = cursor
					.getColumnIndexOrThrow(tCountries_Name);

			if (cursor.moveToFirst()) {
				do {
					String name = cursor.getString(tCountries_Name_Index);
					String code = cursor.getString(tCountries_Code_Index);
					builder.startCountry(code);
					builder.setCountryName(name);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}
	}

	/**
	 * @param db
	 * @param builder
	 * @param c
	 */
	private static void selectCities(SQLiteDatabase db,
			ConfigData.Builder builder, OnProgressCallback c) {
		final String tCountries = CountriesTable.NAME;

		final String tCountries_Id = CountriesTable.Columns._ID;
		final String tCountries_Code = CountriesTable.Columns.CODE;

		final String tCountries_Id_Full = fullFieldName(tCountries,
				tCountries_Id);

		final String tCities = CitiesTable.NAME;

		final String tCities_Id = CitiesTable.Columns._ID;
		final String tCities_CountryId = CitiesTable.Columns.COUNTRY_ID;

		final String tCities_Id_Full = fullFieldName(tCities, tCities_Id);
		final String tCities_CountryId_Full = fullFieldName(tCities,
				tCities_CountryId);
		final String tCityAliases = CityAliasesTable.NAME;
		final String tCityAliases_Name = CityAliasesTable.Columns.NAME;
		final String tCityAliases_CityId = CityAliasesTable.Columns.CITY_ID;

		final String tCityAliases_Name_Full = fullFieldName(tCityAliases,
				tCityAliases_Name);
		final String tCityAliases_CityId_Full = fullFieldName(tCityAliases,
				tCityAliases_CityId);

		final String tCityAliases_Name_Alias = "alias";

		Cursor cursor = db.rawQuery("SELECT " + tCities + ".*, "
				+ tCityAliases_Name_Full + " AS " + tCityAliases_Name_Alias
				+ ", " + tCountries_Code + " FROM " + tCities + " AS "
				+ tCities + " LEFT JOIN " + tCityAliases + " AS "
				+ tCityAliases + " ON " + tCityAliases_CityId_Full + " = "
				+ tCities_Id_Full + " LEFT JOIN " + tCountries + " AS "
				+ tCountries + " ON " + tCities_CountryId_Full + " = "
				+ tCountries_Id_Full + " ORDER BY " + tCities_Id, null);
		try {
			final int tCities_Id_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns._ID);
			final int tCities_Name_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.NAME);
			final int tCities_Url_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.URL);
			final int tCities_Latitude_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.LATITUDE);
			final int tCities_Longitude_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.LONGITUDE);
			final int tCityAliases_Alias_Index = cursor
					.getColumnIndexOrThrow(tCityAliases_Name_Alias);
			final int tCountries_Code_Index = cursor
					.getColumnIndexOrThrow(tCountries_Code);

			long lastId = -1;
			if (cursor.moveToFirst()) {
				int count = cursor.getCount();
				do {
					long id = cursor.getLong(tCities_Id_Index);
					if (id != lastId) {
						URL url;
						try {
							url = new URL(cursor.getString(tCities_Url_Index));
						} catch (MalformedURLException e) {
							Logger
									.w("MalformedURLException when reading ConfigCity");
							lastId = -1;
							continue;
						}
						String name = cursor.getString(tCities_Name_Index);
						String countryCode = cursor
								.getString(tCountries_Code_Index);
						int latitude = cursor.getInt(tCities_Latitude_Index);
						int longitude = cursor.getInt(tCities_Longitude_Index);
						builder.startCity(countryCode);
						builder.setCityName(name);
						builder.setUrl(url);
						builder.setCityLocation(latitude, longitude);
						lastId = id;
						if (c != null) {
							c.onProgress(cursor.getPosition(), count, name);
						}
					}
					String alias = cursor.getString(tCityAliases_Alias_Index);
					if (alias != null && !"null".equals(alias)) {
						builder.addCityAlias(alias);
					}
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}
	}

	private static String fullFieldName(String table, String field) {
		return table + "." + field;
	}

	/**
	 * @return the callback
	 */
	protected final OnProgressCallback getOnProgressCallback() {
		return callback;
	}

}
